package net.wanho.systemSettings.dao.impl;

import net.wanho.commons.dao.impl.BaseDAOImpl;
import net.wanho.commons.exception.DAOException;
import net.wanho.commons.exception.DataAccessException;
import net.wanho.commons.service.impl.BaseServiceImpl;
import net.wanho.systemSettings.dao.EmployeeDAO;
import net.wanho.systemSettings.po.Employee;

import java.util.List;
import java.util.Map;

public class EmployeeDAOImpl extends BaseDAOImpl<Employee> implements EmployeeDAO {
    @Override
    public Employee findById(String id) throws DAOException {
        try {
            String sql = "SELECT \n" +
                    "  e.EMPLOYEE_NAME employeeName,\n" +
                    "  e.PHONE phone,\n" +
                    "  e.EMAIL email,\n" +
                    "  e.STATUS \"status\",\n" +
                    "  e.PARENT_EMPLOYEE_ID parentEmployeeId,\n" +
                    "  e.CREATE_TIME createTime,\n" +
                    "  e.UPDATE_TIME updateTime,\n" +
                    "  e.EMPLOYEE_ID employeeId,\n" +
                    "  l.EMM_PASSWORD \"logIn.emmPassword\",\n" +
                    "  e.DEPARTMENT_ID \"department.departmentId\",\n" +
                    "  d.DEPARTMENT_NAME \"department.departmentName\",\n" +
                    "  e.POSITON_ID \"position.positionId\",\n" +
                    "  p.POSITION_NAME \"position.positionName\",\n" +
                    "  p.POSITION_LEVEL \"position.positionLevel\",\n" +
                    "  p.CREATE_TIME \"position.createTime\",\n" +
                    "  p.UPDATE_TIME \"position.updateTime\" \n" +
                    "FROM\n" +
                    "  employee e \n" +
                    "  RIGHT JOIN log_in l \n" +
                    "    ON e.EMPLOYEE_ID = l.EMPLOYEE_ID \n" +
                    "  LEFT JOIN department d \n" +
                    "    ON d.DEPARTMENT_ID = e.DEPARTMENT_ID \n" +
                    "  LEFT JOIN emm_position p \n" +
                    "    ON p.POSITION_ID = e.POSITON_ID \n" +
                    "WHERE e.EMPLOYEE_ID =?" ;
            Object[] params = {id};
            return super.queryForObject(sql, params);
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw new DAOException(e.getMessage());
        }
    }

    @Override
    public Map<String, Object> count7AndMonth() throws DAOException {
        try {
            String sql = "SELECT\n" +
                    "  SUM(customer7) customer7,\n" +
                    "  SUM(business7) business7,\n" +
                    "  SUM(orders7) orders7,\n" +
                    "  SUM(customer30) customer30\n" +
                    "FROM\n" +
                    "  (SELECT\n" +
                    "    CASE\n" +
                    "      WHEN label = 'customer7'\n" +
                    "      THEN cut\n" +
                    "    END customer7,\n" +
                    "    CASE\n" +
                    "      WHEN label = 'business7'\n" +
                    "      THEN cut\n" +
                    "    END business7,\n" +
                    "    CASE\n" +
                    "      WHEN label = 'orders7'\n" +
                    "      THEN cut\n" +
                    "    END orders7,\n" +
                    "    CASE\n" +
                    "      WHEN label = 'customer30'\n" +
                    "      THEN cut\n" +
                    "    END customer30\n" +
                    "  FROM\n" +
                    "    (SELECT\n" +
                    "      COUNT(0) cut,\n" +
                    "      'customer7' label\n" +
                    "    FROM\n" +
                    "     customer\n" +
                    "    WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(CREATE_TIME)\n" +
                    "    UNION\n" +
                    "    ALL\n" +
                    "    SELECT\n" +
                    "      COUNT(0) cut,\n" +
                    "      'business7' label\n" +
                    "    FROM\n" +
                    "     business\n" +
                    "    WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(UPDATE_TIME)\n" +
                    "    UNION\n" +
                    "    ALL\n" +
                    "    SELECT\n" +
                    "      COUNT(0) cut,\n" +
                    "      'orders7' label\n" +
                    "    FROM\n" +
                    "     orders\n" +
                    "    WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(CREATE_TIME)\n" +
                    "    UNION\n" +
                    "    ALL\n" +
                    "    SELECT\n" +
                    "      COUNT(0) cut,\n" +
                    "      'customer30' label\n" +
                    "    FROM\n" +
                    "     customer\n" +
                    "    WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(CREATE_TIME)) t) tt" ;
            Object[] params = {};
            return super.jdbcTemplate.queryForMap(sql,params) ;
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw new DAOException(e.getMessage());
        }
    }

    @Override
    public List<Map<String, Object>> currMonthCustomerAndBusiness() throws DAOException {
        try {
            String sql = "SELECT\n" +
                    "  '客户' AS \"type\",\n" +
                    "  c.CUSTOMER_NAME \"name\",\n" +
                    "  l.NAME linkName,\n" +
                    "  b.SOURCE_NAME sourceName,\n" +
                    "  c.UPDATE_TIME updateTime\n" +
                    "FROM\n" +
                    "  customer c\n" +
                    "  LEFT JOIN link_man l\n" +
                    "    ON c.PRINPICAL = l.LINK_MAIN_ID\n" +
                    "  LEFT JOIN business_source b\n" +
                    "    ON b.SOURCE_ID = c.SOURCE_ID\n" +
                    "WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(c.CREATE_TIME)\n" +
                    "UNION\n" +
                    "ALL\n" +
                    "SELECT\n" +
                    "  '商机' AS \"type\",\n" +
                    "  b.BUSINESS_NAME \"name\",\n" +
                    "  l.NAME linkName,\n" +
                    "  bs.SOURCE_NAME sourceName,\n" +
                    "  b.UPDATE_TIME updateTime\n" +
                    "FROM\n" +
                    "  business b\n" +
                    "  LEFT JOIN link_man l\n" +
                    "    ON b.LINK_MAIN_ID = l.LINK_MAIN_ID\n" +
                    "  LEFT JOIN business_source bs\n" +
                    "    ON bs.SOURCE_ID = b.BUSINESS_SOURCE_ID\n" +
                    "WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(b.UPDATE_TIME)" ;
            Object[] params = {};
            return super.jdbcTemplate.queryForMapList(sql,params) ;
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw new DAOException(e.getMessage());
        }
    }

    @Override
    public List<Map<String, Object>> selfTask(Integer employeeId) throws DAOException {
        try {
            String sql = "SELECT\n" +
                    "  TOPIC ,\n" +
                    "  END_TIME,\n" +
                    "  STATUS\n" +
                    " FROM individual_task WHERE OPERATOR = ?" ;
            Object[] params = {employeeId};
            return super.jdbcTemplate.queryForMapList(sql,params) ;
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw new DAOException(e.getMessage());
        }
    }
}
